<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2024/4/16
 * Time: 15:53
 */

namespace app\common\lib;


use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class Spreadsheet
{
    protected static $instance;

    public static function getInstance()
    {
        //        if(is_object(self::$instance)){
//            return self::$instance;
//        }


        return new self();
    }

    public function readExcelFile($fileName,$imageCol=[])
    {
        /* 读取excel */
        // $reader = new Xlsx();
        // $spreadsheet = $reader->load($fileName);
        $reader = IOFactory::createReader('Xlsx');
        $spreadsheet = $reader->load($fileName);
        // $reader->setReadDataOnly(true); // 设置后无法获取excel中的图片

        $worksheet = $spreadsheet->getActiveSheet();

        //读取excel图片资源
        $drawings = $worksheet->getDrawingCollection();

        $images = [];
        foreach ($drawings as $k=>$drawing) {
//            var_dump(get_class($drawing));
//            var_dump($drawing->getExtension());
//            var_dump($drawing->getPath());
//            var_dump($drawing->getFilename());
//            var_dump($drawing->getDescription());
//            var_dump('getCoordinates',$drawing->getCoordinates());
            if ($drawing instanceof \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing) {
                $image = $drawing->getImageResource();
//                $imageFile = $drawing->getIndexedFilename();
//                var_dump($imageFile);

                $imageFile = '';
                // 将图片保存到本地
                switch ($drawing->getMimeType()) {
                    case \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::MIMETYPE_PNG :
                        $imageFile = runtime_path('spreadsheet/'.Common::getRequestId().'.png');
                        Common::mkdirs(dirname($imageFile));
                        imagepng($image, $imageFile);
                        break;
                    case \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::MIMETYPE_GIF:
                        $imageFile = runtime_path('spreadsheet/'.Common::getRequestId().'.gif');
                        Common::mkdirs(dirname($imageFile));
                        imagegif($image, $imageFile);
                        break;
                    case \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::MIMETYPE_JPEG :
                        $imageFile = runtime_path('spreadsheet/'.Common::getRequestId().'.jpg');
                        Common::mkdirs(dirname($imageFile));
                        imagejpeg($image, $imageFile);
                        break;
                }
                $imageFile && $images[$drawing->getCoordinates()] = $imageFile;
            }else{
                $imageFile = '';
                switch ($drawing->getExtension()) {
                    case 'jpg':
                    case 'jpeg':
                        $imageFile = runtime_path('spreadsheet/'.Common::getRequestId().'.jpg');
                        Common::mkdirs(dirname($imageFile));
                        $source = imagecreatefromjpeg($drawing->getPath());
                        imagejpeg($source, $imageFile);
                        break;
                    case 'gif':
                        $imageFile = runtime_path('spreadsheet/'.Common::getRequestId().'.gif');
                        Common::mkdirs(dirname($imageFile));
                        $source = imagecreatefromgif($drawing->getPath());
                        imagegif($source, $imageFile);
                        break;
                    case 'png':
                        $imageFile = runtime_path('spreadsheet/'.Common::getRequestId().'.png');
                        Common::mkdirs(dirname($imageFile));
                        $source = imagecreatefrompng($drawing->getPath());
                        imagepng($source, $imageFile);
                        break;
                }
                $imageFile && $images[$drawing->getCoordinates()] = $imageFile;
            }
        }
//        var_dump($images);

        // $worksheet   = $spreadsheet->getSheetByName('testcase');
        // $rawCasedata = $worksheet->toArray();
        $highestRow  = $worksheet->getHighestRow(); // 取得总行数
        $highestColumn = $worksheet->getHighestColumn(); // 取得总列数
        $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn); // 取得总列数

        $excelData = [];
        for ($row = 1; $row <= $highestRow; $row++) {
            for ($col = 1; $col <= $highestColumnIndex; $col++) {
                $colStr = Coordinate::stringFromColumnIndex($col);
                if(in_array($colStr,$imageCol) && isset($images[$colStr.$row])){
                    $excelData[$row][] = $images[$colStr.$row]??'';
                }else{
                    $excelData[$row][] = (string)$worksheet->getCellByColumnAndRow($col, $row)->getValue();
                }

            }
        }

        //var_dump($excelData);
        return $excelData;
    }

    public function writerExcelFile($data,$fileName)
    {
        // 创建新的电子表格对象
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

        // 选择当前活动的工作表
        $sheet = $spreadsheet->getActiveSheet();
        // 获取单元格样式
        $styleArray = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_LEFT, // 水平左对齐
                'vertical' => Alignment::VERTICAL_CENTER, //垂直居中
                'wrapText' => true, // 设置自动换行
            ],
        ];

        $columnMap = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
        $l = 1;
        foreach($data as $row){
            $c = 0;
            foreach($row as $val){
                if(!isset($columnMap[$c])){
                    break 2;
                }

                // 将样式应用到单元格
                $sheet->getStyle($columnMap[$c].$l)->applyFromArray($styleArray);

                //如果是图片
                if(is_file($val) && (strpos($val,'.png') || strpos($val,'.jpg'))){
                    // 设置单元格宽度
                    $sheet->getColumnDimension($columnMap[$c])->setWidth(20); // 设置列宽度为20

                    // 设置单元格高度
                    $sheet->getRowDimension($l)->setRowHeight(100); // 设置第1行高度为30

                    // 创建一个Drawing对象
                    $drawing = new Drawing();
                    $drawing->setOffsetX(10);
                    $drawing->setOffsetY(10);

                    // 设置图片路径
                    $drawing->setPath($val);
                    $drawing->setHeight(100);
                    $drawing->setWidth(100);

                    // 设置图片插入的工作表
                    $drawing->setWorksheet($sheet);

                    // 设置图片的名称
                    //$drawing->setName('Image');

                    // 设置图片在工作表中的位置
                    $drawing->setCoordinates($columnMap[$c].$l); // For example, at cell B2

                }else{
                    // 设置单元格宽度
                    $sheet->getColumnDimension($columnMap[$c])->setWidth(20); // 设置列宽度为20
                    //自适应宽度
                    //$sheet->getColumnDimension($columnMap[$c])->setAutoSize(true);
                    $sheet->setCellValueExplicit($columnMap[$c].$l, $val, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                }
                $c++;
            }
            $l++;
        }

        // 创建一个 Xlsx 写入器
        $writer = new Xlsx($spreadsheet);

        // 保存 Excel 文件到服务器的一个文件中
        return $writer->save($fileName);
    }
}